/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.andersen.database.dao;

import com.andersen.database.entity.ComponentEntity;
import com.andersen.database.entity.EmployeeEntity;
import com.andersen.database.entity.OfficeEntity;
import com.andersen.database.entity.StateEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.stereotype.Component;

/**
 *
 * @author Corvus 11
 */
@Component
public class StateDao extends AbstractDao{
    
    private static final int SORT_BY_FIO = 2;
    private static final int SORT_BY_STATUS = 3;
    
    private StateEntity mapResultSetRow(ResultSet rSet) throws SQLException {
        StateEntity state = new StateEntity();
        state.setId(rSet.getLong("id"));
        state.setComponentId(rSet.getLong("component_id"));
        state.setComputerId(rSet.getLong("computer_id"));
        state.setEmployeeId(rSet.getLong("employee_id"));
        state.setFromDate(rSet.getDate("from_date"));
        state.setToDate(rSet.getDate("to_date"));
        state.setOfficeId(rSet.getLong("office_id"));
        String s = rSet.getString("status");
        if (s != null) state.setStatus(s.charAt(0));
        return state;
    }
    
    public ArrayList<StateEntity> queryForComponentTable(int fromItem, 
                                                         int count, 
                                                         int sortBy, 
                                                         int sortOrder, 
                                                         List<Long> cathegories) {
        ArrayList<StateEntity> result = new ArrayList<StateEntity>();
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        
        LOG.info("StateDao.queryForComponentTable() - sort by = " + sortBy);
        
        StringBuilder sql = new StringBuilder();
        
            sql.append("SELECT * FROM (SELECT c.id AS id, ");
                sql.append("c.description AS description, ");
                sql.append("c.warranty_end_date AS warranty_end_date, ");
                sql.append("c.serial AS serial, ");
                sql.append("c.deleted AS deleted, ");
                sql.append("c.type_id AS type_id, ");
                sql.append("e.name AS employee_name, ");
                sql.append("e.surname AS employee_surname, ");
                sql.append("e.patronymic AS employee_patronymic, ");
                sql.append("o.name AS office_name, ");
                sql.append("s.status AS status ");
            sql.append("FROM state s JOIN component c ON s.component_id = c.id ");
                sql.append("LEFT OUTER JOIN employee e ON s.employee_id = e.id ");
                sql.append("JOIN office o ON s.office_id = o.id ");
                sql.append("WHERE s.to_date IS NULL AND s.computer_id IS NULL ");
            sql.append("UNION SELECT cx.id AS id, ");
                    sql.append("cx.description AS description, ");
                    sql.append("cx.warranty_end_date AS warranty_end_date, ");
                    sql.append("cx.serial AS serial, ");
                    sql.append("cx.deleted AS deleted, ");
                    sql.append("cx.type_id AS type_id, ");
                    sql.append("ex.name AS employee_name, ");
                    sql.append("ex.surname AS employee_surname, ");
                    sql.append("ex.patronymic AS employee_patronymic, ");
                    sql.append("ox.name AS office_name, ");
                    sql.append("sx.status AS STATUS ");
                sql.append("FROM state sx JOIN component cx ON sx.component_id = cx.id ");
                sql.append("JOIN state sxx ON sx.computer_id = sxx.component_id ");
                sql.append("LEFT OUTER JOIN employee ex ON sxx.employee_id = ex.id ");
                sql.append("JOIN office ox ON sxx.office_id = ox.id ");
                sql.append("WHERE sx.to_date IS NULL AND sx.computer_id IS NOT NULL AND sxx.to_date IS NULL) X ");
        
        /*String sql = "SELECT DISTINCT c.id AS id, \n" +
                      "c.description AS description, \n" +
                      "c.warranty_end_date AS warranty_end_date, \n" +
                      "c.serial AS serial, \n" +
                      "c.deleted AS deleted, \n" +
                      "e.name AS employee_name,\n" +
                      "e.surname AS employee_surname,\n" +
                      "e.patronymic AS employee_patronymic,\n" +
                      "o.name AS office_name, \n" +
                      "s.status AS status,\n" +
                      "e1.name AS c_employee_name,\n" +
                      "e1.surname AS c_employee_surname,\n" +
                      "e1.patronymic AS c_employee_patronymic,\n" +
                      "o1.name AS c_office_name\n" +
                  "FROM component c \n" +
                  "JOIN state s ON c.id = s.component_id \n" +
                  "LEFT OUTER JOIN state s1 ON s.computer_id = s1.component_id\n" +
                  "LEFT OUTER JOIN employee e ON s.employee_id = e.id\n" +
                  "LEFT OUTER JOIN employee e1 ON s1.employee_id = e1.id\n" +
                  "LEFT OUTER JOIN office o ON s.office_id = o.id\n" +
                  "LEFT OUTER JOIN office o1 ON s1.office_id = o1.id\n" +
                  "WHERE s.to_date IS NULL AND s1.to_date IS NULL";  */

        if (cathegories != null) {
            sql.append(" WHERE X.type_id IN (");
            for(int i=0;i<cathegories.size();i++) {
                sql.append(cathegories.get(i).toString());
                if (i != (cathegories.size() - 1))
                    sql.append(", ");
            }
            sql.append(")");
        }

        String[] columns = {"X.description",
                            "X.serial",
                            "",
                            "",
                            "X.office_name",
                            "X.warranty_end_date"};
        
        if (sortBy != SORT_BY_FIO && sortBy != SORT_BY_STATUS) {
            appendSortOrder(sql, columns[sortBy], sortOrder);
        } else {
            if (sortBy == SORT_BY_FIO){
                if (sortOrder == SORT_ORDER_ASC)
                    sql.append(" ORDER BY X.employee_name, X.employee_surname, X.employee_patronymic");
                else 
                    sql.append(" ORDER BY X.employee_name DESC, X.employee_surname DESC, X.employee_patronymic DESC");
            } else {
                if (sortOrder == SORT_ORDER_ASC)
                    sql.append(" ORDER BY X.deleted, X.status");
                else 
                    sql.append(" ORDER BY X.deleted DESC, X.status DESC");
            }
        }
        
        appendPaginationParams(sql, fromItem, count);
            
        sql.append(";");
        
        LOG.info("query: ");
        LOG.info(sql.toString());
          
        
        try {
  
          conn = dataSource.getConnection();
          st = conn.prepareStatement(sql.toString());
          rs = st.executeQuery();
          while(rs.next()) {
              StateEntity state = new StateEntity();
              
              String stat = rs.getString("status");
              if (stat == null || stat.isEmpty())
                state.setStatus(StateEntity.STATUS_OK);
              else state.setStatus(stat.charAt(0));
              
              ComponentEntity component = new ComponentEntity();
              
              component.setId(rs.getLong("id"));
              component.setDescription(rs.getString("description"));
              component.setWarrantyEndDate(rs.getDate("warranty_end_date"));
              component.setSerial(rs.getString("serial"));
              component.setDeleted(rs.getBoolean("deleted"));
              
              EmployeeEntity employee = new EmployeeEntity();
              
              employee.setName(rs.getString("employee_name"));
              employee.setSurname(rs.getString("employee_surname"));
              employee.setPatronymic(rs.getString("employee_patronymic"));
              
              OfficeEntity office = new OfficeEntity();
              office.setName(rs.getString("office_name"));
              
              employee.setOffice(office);
              state.setEmployee(employee);
              state.setComponent(component);
              
              result.add(state);
          }
            
        } catch (SQLException exc) {
                LOG.error("Ошибка при выполнении queryForComponentTable",exc);
                return null;
        } finally {
                safeClose(rs);
                safeClose(st);
                safeClose(conn);
            }
        
        return result;
    }
    
    public long countCurrentStates(List<Long> cathegories) {
        long count = -1;
        
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        
        StringBuilder sql = new StringBuilder();
        
        sql.append("SELECT COUNT(s.id) ");
        sql.append("FROM state s ");
        sql.append("JOIN component c ON s.component_id = c.id ");
        sql.append("WHERE s.to_date IS NULL");
        
        if (cathegories != null) {
            sql.append(" AND c.type_id IN (");
            for(int i=0;i<cathegories.size();i++) {
                sql.append(cathegories.get(i).toString());
                if (i != (cathegories.size() - 1))
                    sql.append(", ");
            }
            sql.append(")");
        }
        sql.append(";");
        
        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString());
            rs = st.executeQuery();
            if (rs.first()) {
                count = rs.getLong(1);
            }
        } catch (SQLException exc) {
                LOG.error("Ошибка при выполнении countCurrentStates",exc);
                return -1l;
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
        return count;
    }
    
    public boolean updateState(StateEntity state) {
        boolean ok = false;
        
        Connection conn = null;
        PreparedStatement st = null;
        
        StringBuilder sql = new StringBuilder();
        
        LOG.info("StateDao.updateState() - updating state:" + state);
        
        sql.append("UPDATE state ");
            sql.append("SET component_id = ?, ");
            sql.append("from_date = ?, ");
            sql.append("to_date = ?, ");
            sql.append("employee_id = ?, ");
            sql.append("computer_id = ?, ");
            sql.append("office_id = ?, ");
            sql.append("status = ? ");
        sql.append("WHERE id = (?);");
        
        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString());
            st.setLong(1, state.getComponentId());
            st.setDate(2, state.getFromDate());
            if (state.getToDate() != null) st.setDate(3, state.getToDate());
            else st.setNull(3, java.sql.Types.NULL);
            if (state.getEmployeeId() != ID_NOT_DEFINED) st.setLong(4, state.getEmployeeId());
            else st.setNull(4, java.sql.Types.NULL);
            if (state.getComputerId() != ID_NOT_DEFINED) st.setLong(5, state.getComputerId());
            else st.setNull(5, java.sql.Types.NULL);
            if (state.getOfficeId() != ID_NOT_DEFINED) st.setLong(6, state.getOfficeId());
            else st.setNull(6, java.sql.Types.NULL);
            if (state.getStatus() != StateEntity.STATUS_OK) st.setString(7, String.valueOf(state.getStatus()));
            else st.setNull(7, java.sql.Types.NULL);
            st.setLong(8, state.getId());
        
            ok = (st.executeUpdate() > 0);
            
        } catch (SQLException exc) {
                LOG.error("Error with StateDao.updateState()",exc);
                return false;
        } finally {
            safeClose(st);
            safeClose(conn);
        }
            
        return ok;
    }
    
    public long insertState(StateEntity state) {
        long newKey = ID_INVALID;
        
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        
        StringBuilder sql = new StringBuilder();
        
        sql.append("INSERT INTO state(component_id, ");
            sql.append("from_date, ");
            sql.append("to_date, "); 
            sql.append("employee_id, ");
            sql.append("computer_id, ");
            sql.append("office_id, ");
            sql.append("status) ");
        sql.append("VALUE(?,?,?,?,?,?,?);");
                       
        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
            st.setLong(1, state.getComponentId());
            st.setDate(2, state.getFromDate());
            st.setDate(3, state.getToDate());
            if (state.getEmployeeId() != ID_NOT_DEFINED) st.setLong(4, state.getEmployeeId());
            else st.setNull(4, java.sql.Types.NULL);
            if (state.getComputerId() != ID_NOT_DEFINED) st.setLong(5, state.getComputerId());
            else st.setNull(5, java.sql.Types.NULL);
            if (state.getOfficeId() != ID_NOT_DEFINED) st.setLong(6, state.getOfficeId());
            else st.setNull(6, java.sql.Types.NULL);
            if (state.getStatus() != StateEntity.STATUS_OK) st.setString(7, String.valueOf(state.getStatus()));
            else st.setNull(7, java.sql.Types.NULL);
        
            if (st.executeUpdate() > 0) {
                rs = st.getGeneratedKeys();
                rs.first();
                newKey = rs.getLong(1);
            }
            
        } catch (SQLException exc) {
                LOG.error("Ошибка при выполнении insertState",exc);
                return ID_INVALID;
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
            
        return newKey;
    }
    
    public StateEntity getLastStateOfComponent(long componentId) {
        LOG.info("getting State from db with componentId " + componentId );

        String sql = "SELECT * FROM state s WHERE s.component_id = ? AND s.to_date IS NULL";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        StateEntity state = null;
        
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setLong(1, componentId);

            rs = ps.executeQuery();
            if (rs.first()) {
                state = mapResultSetRow(rs);
            }
            LOG.info("state: " + state);
        } catch (SQLException e) {
            LOG.error("StateDao.getLastStateOfComponent() - error: ",e);
        } finally {
            safeClose(rs);
            safeClose(ps);
            safeClose(conn);
        }
        return state;
    }
}
    